const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const QRCode = require('qrcode');
const multer = require('multer');
const cors = require('cors');

const app = express();
const PORT = process.env.PORT || 3000;

// 中间件
app.use(cors());
app.use(express.json());
app.use(express.static('public'));
app.use('/audio', express.static('public/audio'));
app.use('/qrcodes', express.static('public/qrcodes'));

// 数据库初始化
const db = new sqlite3.Database('server/db/safety_voice.db');

db.serialize(() => {
  // 员工表
  db.run(`CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    phone TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    name TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  // 语音消息表
  db.run(`CREATE TABLE IF NOT EXISTS voice_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    sender_type TEXT CHECK(sender_type IN ('employee', 'family')),
    audio_path TEXT,
    photo_path TEXT,
    qr_code_path TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees (id)
  )`);

  // 互动统计表
  db.run(`CREATE TABLE IF NOT EXISTS interaction_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    employee_voice_count INTEGER DEFAULT 0,
    family_voice_count INTEGER DEFAULT 0,
    family_photo_count INTEGER DEFAULT 0,
    last_interaction DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees (id)
  )`);
});

// 文件上传配置
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    if (file.mimetype.startsWith('audio/')) {
      cb(null, 'public/audio/');
    } else if (file.mimetype.startsWith('image/')) {
      cb(null, 'public/audio/');
    }
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, file.fieldname + '-' + uniqueSuffix + '.' + file.originalname.split('.').pop());
  }
});
const upload = multer({ storage: storage });

// 员工注册
app.post('/api/register', (req, res) => {
  const { phone, password, name } = req.body;
  
  db.run('INSERT INTO employees (phone, password, name) VALUES (?, ?, ?)', 
    [phone, password, name], function(err) {
    if (err) {
      if (err.code === 'SQLITE_CONSTRAINT') {
        return res.status(400).json({ error: '手机号已注册' });
      }
      return res.status(500).json({ error: '注册失败' });
    }
    
    // 初始化互动统计
    db.run('INSERT INTO interaction_stats (employee_id) VALUES (?)', [this.lastID]);
    
    res.json({ success: true, employeeId: this.lastID });
  });
});

// 员工登录
app.post('/api/login', (req, res) => {
  const { phone, password } = req.body;
  
  db.get('SELECT * FROM employees WHERE phone = ? AND password = ?', 
    [phone, password], (err, row) => {
    if (err) {
      return res.status(500).json({ error: '登录失败' });
    }
    if (!row) {
      return res.status(401).json({ error: '手机号或密码错误' });
    }
    
    res.json({ success: true, employee: row });
  });
});

// 上传员工语音并生成二维码
app.post('/api/upload-employee-voice', upload.single('voice'), (req, res) => {
  const { employeeId } = req.body;
  const audioPath = req.file.path.replace('public/', '');
  
  db.run('INSERT INTO voice_messages (employee_id, sender_type, audio_path) VALUES (?, ?, ?)',
    [employeeId, 'employee', audioPath], function(err) {
    if (err) {
      return res.status(500).json({ error: '保存语音失败' });
    }
    
    // 更新统计
    db.run('UPDATE interaction_stats SET employee_voice_count = employee_voice_count + 1, last_interaction = CURRENT_TIMESTAMP WHERE employee_id = ?', [employeeId]);
    
    // 生成二维码
    const qrData = `${req.protocol}://${req.get('host')}/family/${employeeId}/${this.lastID}`;
    const qrPath = `public/qrcodes/employee-${employeeId}-${this.lastID}.png`;
    
    QRCode.toFile(qrPath, qrData, {
      width: 300,
      margin: 2,
      color: {
        dark: '#000000',
        light: '#FFFFFF'
      }
    }, (err) => {
      if (err) {
        return res.status(500).json({ error: '生成二维码失败' });
      }
      
      // 保存二维码路径
      db.run('UPDATE voice_messages SET qr_code_path = ? WHERE id = ?', 
        [qrPath.replace('public/', ''), this.lastID]);
      
      res.json({ 
        success: true, 
        qrCode: qrPath.replace('public/', ''),
        messageId: this.lastID
      });
    });
  });
});

// 家人上传语音/照片
app.post('/api/upload-family-message/:employeeId', upload.fields([
  { name: 'voice', maxCount: 1 },
  { name: 'photo', maxCount: 1 }
]), (req, res) => {
  const { employeeId } = req.params;
  const voiceFile = req.files.voice ? req.files.voice[0] : null;
  const photoFile = req.files.photo ? req.files.photo[0] : null;
  
  if (!voiceFile && !photoFile) {
    return res.status(400).json({ error: '请至少上传语音或照片' });
  }
  
  const audioPath = voiceFile ? voiceFile.path.replace('public/', '') : null;
  const photoPath = photoFile ? photoFile.path.replace('public/', '') : null;
  
  db.run('INSERT INTO voice_messages (employee_id, sender_type, audio_path, photo_path) VALUES (?, ?, ?, ?)',
    [employeeId, 'family', audioPath, photoPath], function(err) {
    if (err) {
      return res.status(500).json({ error: '保存消息失败' });
    }
    
    // 更新统计
    let updateQuery = 'UPDATE interaction_stats SET ';
    let params = [];
    
    if (voiceFile) {
      updateQuery += 'family_voice_count = family_voice_count + 1';
      params.push('voice');
    }
    if (photoFile) {
      if (voiceFile) updateQuery += ', ';
      updateQuery += 'family_photo_count = family_photo_count + 1';
      params.push('photo');
    }
    
    updateQuery += ', last_interaction = CURRENT_TIMESTAMP WHERE employee_id = ?';
    params.push(employeeId);
    
    db.run(updateQuery, params);
    
    // 生成二维码
    const qrData = `${req.protocol}://${req.get('host')}/employee/${employeeId}/${this.lastID}`;
    const qrPath = `public/qrcodes/family-${employeeId}-${this.lastID}.png`;
    
    QRCode.toFile(qrPath, qrData, {
      width: 300,
      margin: 2,
      color: {
        dark: '#000000',
        light: '#FFFFFF'
      }
    }, (err) => {
      if (err) {
        return res.status(500).json({ error: '生成二维码失败' });
      }
      
      db.run('UPDATE voice_messages SET qr_code_path = ? WHERE id = ?', 
        [qrPath.replace('public/', ''), this.lastID]);
      
      res.json({ 
        success: true, 
        qrCode: qrPath.replace('public/', ''),
        messageId: this.lastID
      });
    });
  });
});

// 获取员工消息列表
app.get('/api/employee-messages/:employeeId', (req, res) => {
  const { employeeId } = req.params;
  
  db.all(`SELECT vm.*, e.name as employee_name 
          FROM voice_messages vm 
          JOIN employees e ON vm.employee_id = e.id 
          WHERE vm.employee_id = ? 
          ORDER BY vm.created_at DESC`, 
    [employeeId], (err, rows) => {
    if (err) {
      return res.status(500).json({ error: '获取消息失败' });
    }
    
    res.json({ messages: rows });
  });
});

// 获取互动统计
app.get('/api/interaction-stats/:employeeId', (req, res) => {
  const { employeeId } = req.params;
  
  db.get('SELECT * FROM interaction_stats WHERE employee_id = ?', 
    [employeeId], (err, row) => {
    if (err) {
      return res.status(500).json({ error: '获取统计失败' });
    }
    
    res.json({ stats: row || { employee_voice_count: 0, family_voice_count: 0, family_photo_count: 0 } });
  });
});

// 家人页面路由
app.get('/family/:employeeId/:messageId', (req, res) => {
  res.sendFile(path.join(__dirname, '../public/family.html'));
});

// 员工查看家人消息路由
app.get('/employee/:employeeId/:messageId', (req, res) => {
  res.sendFile(path.join(__dirname, '../public/employee.html'));
});

app.listen(PORT, () => {
  console.log(`服务器运行在 http://localhost:${PORT}`);
});
